Dynamic creation of offline MDF

Sometimes you don't want to have to be online just to compile your programs, or you might not have access to your production database from your CI systems. With FSharp.Data.SqlClient you can use a local .MDF file as the compile-time connection string, and then use a different connection string when you deploy your application.

A connection string to a local .MDF file might look like this:

1: 
2: 
3: 
4: 
5:

open FSharp.Data

[<Literal>]
let compileConnectionString =
    @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\git\Project1\Database1.mdf;Integrated Security=True"

However, binary files like this are difficult to diff/merge when working with multiple developers, so you might not want to check them in. Wouldn't it be nice to store your schema in a plain text file, and have it dynamically create the MDF file for compile time?

Well the following scripts can do that for your project.

First create a file called createDb.ps1 and place it in an SQL subfolder in your project (you can place it in the project root to, if you want):

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
36: 
37: 
38:

param(
  [Parameter(Mandatory=$true)][String]$DbName,
  [Parameter(Mandatory=$true)][String]$DbScript
)

$detach_db_sql = @"
IF (SELECT COUNT(*) FROM sys.databases WHERE name = '$DbName') > 0
  EXEC sp_detach_db @dbname = N'$DbName'
"@

$detach_db_sql | Out-File "detachdb.sql"
sqlcmd -S "(LocalDB)\MSSQLLocalDB" -i "detachdb.sql"
Remove-Item "detachdb.sql"

if (Test-Path "$PSScriptRoot\$DbName.mdf") { Remove-Item "$PSScriptRoot\$DbName.mdf" }
if (Test-Path "$PSScriptRoot\$DbName.ldf") { Remove-Item "$PSScriptRoot\$DbName.ldf" }

$create_db_sql = @"
CREATE DATABASE $DbName
ON (
  NAME = ${DbName}_dat,
  FILENAME = '$PSScriptRoot\$DbName.mdf'
)
LOG ON (
  NAME = ${DbName}_log,
  FILENAME = '$PSScriptRoot\$DbName.ldf'
)
"@

$create_db_sql | Out-File "createdb.sql"
sqlcmd -S "(LocalDB)\MSSQLLocalDB" -i "createdb.sql"
Remove-Item "createdb.sql"

sqlcmd -S "(LocalDB)\MSSQLLocalDB" -i "$DbScript"

$detach_db_sql | Out-File "detachdb.sql"
sqlcmd -S "(LocalDB)\MSSQLLocalDB" -i "detachdb.sql"
Remove-Item "detachdb.sql"

Then change your connection string to look like this

1: 
2: 
3: 
4: 
5: 
6: 
7:

[<Literal>]
let compileConnectionString =
    @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" + __SOURCE_DIRECTORY__ + @"\Database1.mdf;Integrated Security=True;Connect Timeout=10"

type Foo = SqlCommandProvider<"SELECT * FROM Foo", compileConnectionString>

let myResults = (new Foo("Use your Runtime connectionString here")).Execute()

Lastly, edit your .fsproj file and add the following to the very end right before </Project>:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21:

<ItemGroup>
  <SqlFiles Include="**\*.sql" />
  <BuildDbPsScript Include="SQL\createDb.ps1" />
  <BuildDbSqlScripts Include="SQL\create_myDb1.sql" DbName="Db1" />
  <BuildDbSqlScripts Include="SQL\create_myDb2.sql" DbName="Db2" />
  <UpToDateCheckInput Include="@(SqlFiles)" />
  <UpToDateCheckInput Include="@(BuildDbPsScript)" />
  <UpToDateCheckInput Include="@(BuildDbSqlScripts)" />
  <UpToDateCheckInput Include="@(BuildDbSqlScripts -> 'SQL\%(DbName).mdf')" />
  <UpToDateCheckInput Include="@(BuildDbSqlScripts -> 'SQL\%(DbName).ldf')" />
</ItemGroup>

<Target Name="BuildDb" BeforeTargets="BeforeBuild" Inputs="@(BuildDbSqlScripts);@(BuildDbPsScript)" Outputs="SQL\%(BuildDbSqlScripts.DbName).mdf;SQL\%(BuildDbSqlScripts.DbName).ldf">
  <Message Text="DB files missing or outdated. Building out database %(BuildDbSqlScripts.DbName) using script %(BuildDbSqlScripts.Identity)" Importance="High" />
  <Exec Command="PowerShell -NoProfile -ExecutionPolicy Bypass -Command &quot;&amp; { @(BuildDbPsScript) -DbName %(BuildDbSqlScripts.DbName) -DbScript %(BuildDbSqlScripts.Identity) }&quot;" />
</Target>

<Target Name="TouchProjectFileIfSqlOrDbChanged" BeforeTargets="BeforeBuild" Inputs="@(SqlFiles);@(BuildDbPsScript);@(BuildDbSqlScripts)" Outputs="$(MSBuildProjectFile)">
  <Message Text="SQL or DB files changed. Changing project file modification time to force recompilation." Importance="High" />
  <Exec Command="PowerShell -NoProfile -ExecutionPolicy Bypass -Command &quot;(dir $(MSBuildProjectFile)).LastWriteTime = Get-Date&quot;" />
</Target>

Now when you build, it will create the databases SQL\Db1.mdf and SQL\Db2.mdf using the scripts SQL\create_myDb1.sql and SQL\create_myDb2.sql. It will then compile against this dynamically generated MDF file so you'll get full static type checking without the hassle of having to have an internet connection, or deal with binary .MDF files!

Furthermore, the .fsproj edits above give the following benefits: The DBs are rebuilt if their corresponding SQL scripts have changed, or if the PowerShell script has changed The project is rebuilt if the PowerShell script has changed The project is rebuilt if any SQL file has changed (both the database creation scripts, and any other SQL scripts that SqlClient might use though the SqlFile type provider) Incremental build - each database is only built if its corresponding SQL script or the PowerShell script has changed

When it comes to actually making the database creation scripts (such as the create_myDb1.sql in the example above), you can do this if you use SQL Server Management Studio (SSMS): Connect to the database you want to copy Right-click the database and select Tasks -> Generate scripts Select what you need to be exported (for example, everything except Users). If SqlClient throws errors when connecting to your local database, you might be missing important objects from your database. Make sure everything you need is enabled in SSMS under Tools -> Options -> SQL Server Object Explorer -> Scripting. For example, if you have indexed views and use the WITH (NOEXPAND) hint in your SQL, you need the indexes too, which are not enabled by default. In this case, enable "Script indexes" under the "Table and view options" heading.

namespace Microsoft.FSharp.Data

Multiple items
type LiteralAttribute =
inherit Attribute
new : unit -> LiteralAttribute

Full name: Microsoft.FSharp.Core.LiteralAttribute

--------------------
new : unit -> LiteralAttribute

val compileConnectionString : string

Full name: Dynamic local db.compileConnectionString

module String

from Microsoft.FSharp.Core

type Foo = obj

Full name: Dynamic local db.Foo

val myResults : obj

Full name: Dynamic local db.myResults